﻿-- Table: tbltmp_po_rate
-- --Create table
-- DROP TABLE tbltmp_po_rate;
-- 
-- CREATE TABLE tbltmp_po_rate
-- (
--   id integer NOT NULL,
--   rate numeric NOT NULL DEFAULT 1.00,
--   CONSTRAINT tbltmp_po_rate_pkey PRIMARY KEY (id ),
--   CONSTRAINT po_rate_constraint_po FOREIGN KEY (id)
--       REFERENCES purchase_order (id) MATCH SIMPLE
--       ON UPDATE CASCADE ON DELETE CASCADE
-- )
-- WITH (
--   OIDS=FALSE
-- );
-- ALTER TABLE tbltmp_po_rate
--   OWNER TO openerp;
--delete from tbltmp_po_rate
---------- 2nd Run
--Insert into tbltmp_po_rate select id,rate from vwpo_rate

--Trigger Function
-- Function: trgfnwhenupdatepo_updatepol()

-- DROP FUNCTION trgfnwhenupdatepo_updatepol();

CREATE OR REPLACE FUNCTION trgfnwheninsertpo_insertporate()
  RETURNS trigger AS
$BODY$ 
		Begin
			if TG_OP='INSERT' then
				insert into tbltmp_po_rate (id) values (new.id);
			elseif new.state='done' and (old.state<>new.state) then
				update tbltmp_po_rate tpr set rate=(Select rate from vwpo_rate where id=new.id) where tpr.id=new.id;
			end if;
			return new;
		end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trgfnwheninsertpo_insertporate()
  OWNER TO openerp;
  
-- CREATE TRIGGER trgwheninsertpo_insertinto_po_rate
  AFTER INSERT OR UPDATE
  ON purchase_order
  FOR EACH ROW
  EXECUTE PROCEDURE trgfnwheninsertpo_insertporate();

--explain analyze